﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC01BO
    /// </summary>
    public class VYC01BO : DBConnectionBO
    {
        public VYC01BO()
            : base()
        {
        }

        /// <summary>
        /// Lay danh sach cac du lieu trong VYC01
        /// </summary>
        /// <returns></returns>
        public List<ModelVYC01> GetAllList()
        {
            var list = new List<ModelVYC01>();
            const string query = "SELECT [Id],[IdSubLoaiTour],[IdLoaiTour],[UniqueId],[TieuDe],[AnhDaiDien],[AnhPanorama],[TomTat],[NoiDung],[SoCho],[NgayDi],[NgayDen],[IdNoiDi],[IdNoiDen],[Gia],[Tag],[NgayCapNhat],[TrangThai],[IdUser],[HienThiPanoramaLon],[HienThiPanoramaNho],[IdTourDocDao],[IdTourLeTet],[IdTourDiemDen] FROM [VYC-01]";
            var sqlCommand = new SqlCommand(query, connection);            
            var reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                var model = new ModelVYC01();

                model.Id = reader["Id"].ToString();
                model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                model.UniqueId = reader["UniqueId"].ToString();
                model.TieuDe = reader["TieuDe"].ToString();                
                model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                model.AnhPanorama = reader["AnhPanorama"].ToString();
                model.TomTat = reader["TomTat"].ToString();
                model.NoiDung = reader["NoiDung"].ToString();
                model.SoCho = Convert.ToInt32(reader["SoCho"]);
                model.NgayDi = (DateTime)reader["NgayDi"];
                model.NgayDen = (DateTime)reader["NgayDen"];
                model.IdNoiDi = Convert.ToInt32(reader["IdNoiDi"]);
                model.IdNoiDen = Convert.ToInt32(reader["IdNoiDen"]);
                model.Gia = Convert.ToInt32(reader["Gia"]);
                model.Tag = reader["Tag"].ToString();
                model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];
                model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                model.IdUser = reader["IdUser"].ToString();
                model.HienThiPanoramaLon = Convert.ToInt32(reader["HienThiPanoramaLon"]);
                model.HienThiPanoramaNho = Convert.ToInt32(reader["HienThiPanoramaNho"]);
                model.IdTourDocDao = reader["IdTourDocDao"].ToString();
                model.IdTourLeTet = reader["IdTourLeTet"].ToString();
                model.IdTourDiemDen = Convert.ToInt32(reader["IdSubLoaiTour"]);
                
                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC01 model)
        {
            var sqlParam = "INSERT INTO [VYC-01]([Id],[IdSubLoaiTour],[IdLoaiTour],[UniqueId],[TieuDe],[AnhDaiDien],[AnhPanorama],[TomTat],[NoiDung],[SoCho],[NgayDi],[NgayDen],[IdNoiDi],[IdNoiDen],[Gia],[Tag],[NgayCapNhat],[TrangThai],[IdUser],[HienThiPanoramaLon],[HienThiPanoramaNho],[IdTourDocDao],[IdTourLeTet],[IdTourDiemDen]) VALUES (@id, @idsubloaitour, @idloaitour, @uniqueid, @tieude, @anhdaidien, @anhpanorama, @tomtat, @noidung, @socho, @ngaydi, @ngayden, @idnoidi, @idnoiden, @gia, @tag, GETDATE(), @trangthai, @iduser, @hienthipanoramalon, @hienthipanoramanho, @idtourdocdao, @idtourletet, @idtourdiemden)";
            var sqlCommand = new SqlCommand(sqlParam, connection);

            sqlCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = model.Id;
            sqlCommand.Parameters.Add("@idsubloaitour", SqlDbType.Int).Value = model.IdSubLoaiTour;
            sqlCommand.Parameters.Add("@idloaitour", SqlDbType.Int).Value = model.IdLoaiTour;
            sqlCommand.Parameters.Add("@uniqueid", SqlDbType.VarChar).Value = model.UniqueId;
            sqlCommand.Parameters.Add("@tieude", SqlDbType.NVarChar).Value = model.TieuDe;
            sqlCommand.Parameters.Add("@anhdaidien", SqlDbType.NVarChar).Value = model.AnhDaiDien;
            sqlCommand.Parameters.Add("@anhpanorama", SqlDbType.NVarChar).Value = model.AnhPanorama;
            sqlCommand.Parameters.Add("@tomtat", SqlDbType.NVarChar).Value = model.TomTat;
            sqlCommand.Parameters.Add("@noidung", SqlDbType.NText).Value = model.NoiDung;
            sqlCommand.Parameters.Add("@socho", SqlDbType.Int).Value = model.SoCho;
            sqlCommand.Parameters.Add("@ngaydi", SqlDbType.DateTime).Value = model.NgayDi;
            sqlCommand.Parameters.Add("@ngayden", SqlDbType.DateTime).Value = model.NgayDen;
            sqlCommand.Parameters.Add("@idnoidi", SqlDbType.Int).Value = model.IdNoiDi;
            sqlCommand.Parameters.Add("@idnoiden", SqlDbType.Int).Value = model.IdNoiDen;
            sqlCommand.Parameters.Add("@gia", SqlDbType.Decimal).Value = model.Gia;
            sqlCommand.Parameters.Add("@tag", SqlDbType.NVarChar).Value = model.Tag;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            sqlCommand.Parameters.Add("@iduser", SqlDbType.VarChar).Value = model.IdUser;
            sqlCommand.Parameters.Add("@hienthipanoramalon", SqlDbType.Bit).Value = model.HienThiPanoramaLon;
            sqlCommand.Parameters.Add("@hienthipanoramanho", SqlDbType.Bit).Value = model.HienThiPanoramaNho;
            sqlCommand.Parameters.Add("@idtourdocdao", SqlDbType.VarChar).Value = model.HienThiPanoramaLon;
            sqlCommand.Parameters.Add("@idtourletet", SqlDbType.VarChar).Value = model.HienThiPanoramaLon;
            sqlCommand.Parameters.Add("@idtourdiemden", SqlDbType.Int).Value = model.IdTourDiemDen;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC01 model)
        {
            var query = "UPDATE [VYC-01] SET " +
                        "[IdSubLoaiTour] = @idsubloaitour" +
                        ",[IdLoaiTour] = @idloaitour" +
                        ",[UniqueId] = @uniqueid" +
                        ",[TieuDe] = @tieude" +
                        ",[AnhDaiDien] = @anhdaidien" +
                        ",[AnhPanorama] = @anhpanorama" +
                        ",[TomTat] = @tomtat" +
                        ",[NoiDung] = @noidung" +
                        ",[SoCho] = @socho" +
                        ",[NgayDi] = @ngaydi" +
                        ",[NgayDen] = @ngayden" +
                        ",[IdNoiDi] = @idnoidi" +
                        ",[IdNoiDen] = @idnoiden" +
                        ",[Gia] = @gia" +
                        ",[Tag] = @tag" +
                        ",NgayCapNhat = GETDATE(), TrangThai = @trangthai" +
                        ",[IdUser] = @iduser" +
                        ",[HienThiPanoramaLon] = @hienthipanoramalon" +
                        ",[HienThiPanoramaNho] = @hienthipanoramanho" +
                        ",[IdTourDocDao] = @idtourdocdao" +
                        ",[IdTourLeTet] = @idtourletet" +
                        ",[IdTourDiemDen] = @idtourdiemden" +
                        " WHERE Id like @id";

            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = model.Id;
            sqlCommand.Parameters.Add("@idsubloaitour", SqlDbType.Int).Value = model.IdSubLoaiTour;
            sqlCommand.Parameters.Add("@idloaitour", SqlDbType.Int).Value = model.IdLoaiTour;
            sqlCommand.Parameters.Add("@uniqueid", SqlDbType.VarChar).Value = model.UniqueId;
            sqlCommand.Parameters.Add("@tieude", SqlDbType.NVarChar).Value = model.TieuDe;
            sqlCommand.Parameters.Add("@anhdaidien", SqlDbType.NVarChar).Value = model.AnhDaiDien;
            sqlCommand.Parameters.Add("@anhpanorama", SqlDbType.NVarChar).Value = model.AnhPanorama;
            sqlCommand.Parameters.Add("@tomtat", SqlDbType.NVarChar).Value = model.TomTat;
            sqlCommand.Parameters.Add("@noidung", SqlDbType.NText).Value = model.NoiDung;
            sqlCommand.Parameters.Add("@socho", SqlDbType.Int).Value = model.SoCho;
            sqlCommand.Parameters.Add("@ngaydi", SqlDbType.DateTime).Value = model.NgayDi;
            sqlCommand.Parameters.Add("@ngayden", SqlDbType.DateTime).Value = model.NgayDen;
            sqlCommand.Parameters.Add("@idnoidi", SqlDbType.Int).Value = model.IdNoiDi;
            sqlCommand.Parameters.Add("@idnoiden", SqlDbType.Int).Value = model.IdNoiDen;
            sqlCommand.Parameters.Add("@gia", SqlDbType.Decimal).Value = model.Gia;
            sqlCommand.Parameters.Add("@tag", SqlDbType.NVarChar).Value = model.Tag;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            sqlCommand.Parameters.Add("@iduser", SqlDbType.VarChar).Value = model.IdUser;
            sqlCommand.Parameters.Add("@hienthipanoramalon", SqlDbType.Bit).Value = model.HienThiPanoramaLon;
            sqlCommand.Parameters.Add("@hienthipanoramanho", SqlDbType.Bit).Value = model.HienThiPanoramaNho;
            sqlCommand.Parameters.Add("@idtourdocdao", SqlDbType.VarChar).Value = model.IdTourDocDao;
            sqlCommand.Parameters.Add("@idtourletet", SqlDbType.VarChar).Value = model.IdTourLeTet;
            sqlCommand.Parameters.Add("@idtourdiemden", SqlDbType.Int).Value = model.IdTourDiemDen;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC01 model)
        {
            var query = "DELETE FROM [VYC-01] " +
                        " WHERE Id like @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Lay danh sach cac du lieu trong VYC01
        /// </summary>
        /// <returns></returns>
        public List<ModelVYC01> GetById(string id)
        {
            var list = new List<ModelVYC01>();
            var query = "SELECT [Id],[IdSubLoaiTour],[IdLoaiTour],[UniqueId],[TieuDe],[AnhDaiDien],[AnhPanorama],[TomTat],[NoiDung],[SoCho],[NgayDi],[NgayDen],[IdNoiDi],[IdNoiDen],[Gia],[Tag],[NgayCapNhat],[TrangThai],[IdUser],[HienThiPanoramaLon],[HienThiPanoramaNho],[IdTourDocDao],[IdTourLeTet],[IdTourDiemDen] FROM [VYC-01] WHERE Id like '" + id + "'";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC01();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? string.Empty : dataTable.Rows[i][0].ToString();
                model.IdSubLoaiTour = (dataTable.Rows[i][1] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][1]);
                model.IdLoaiTour = (dataTable.Rows[i][2] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][2]);
                model.UniqueId = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.TieuDe = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString();
                model.AnhDaiDien = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.AnhPanorama = (dataTable.Rows[i][6] == DBNull.Value) ? string.Empty : dataTable.Rows[i][6].ToString();
                model.TomTat = (dataTable.Rows[i][7] == DBNull.Value) ? string.Empty : dataTable.Rows[i][7].ToString();
                model.NoiDung = (dataTable.Rows[i][8] == DBNull.Value) ? string.Empty : dataTable.Rows[i][8].ToString();
                model.SoCho = (dataTable.Rows[i][9] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][9]);
                model.NgayDi = (dataTable.Rows[i][10] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][10];
                model.NgayDen = (dataTable.Rows[i][11] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][11];
                model.IdNoiDi = (dataTable.Rows[i][12] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][12]);
                model.IdNoiDen = (dataTable.Rows[i][13] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][13]);
                model.Gia = (dataTable.Rows[i][14] == DBNull.Value) ? 0 : Convert.ToDecimal(dataTable.Rows[i][14]);
                model.Tag = (dataTable.Rows[i][15] == DBNull.Value) ? string.Empty : dataTable.Rows[i][15].ToString();
                model.NgayCapNhat = (dataTable.Rows[i][16] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][16];
                model.TrangThai = Convert.ToInt32(dataTable.Rows[i][17]);
                model.IdUser = (dataTable.Rows[i][18] == DBNull.Value) ? string.Empty : dataTable.Rows[i][18].ToString();
                model.HienThiPanoramaLon = (dataTable.Rows[i][19] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][19]);
                model.HienThiPanoramaNho = (dataTable.Rows[i][20] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][20]);
                model.IdTourDocDao = (dataTable.Rows[i][21] == DBNull.Value) ? string.Empty : dataTable.Rows[i][21].ToString();
                model.IdTourLeTet = (dataTable.Rows[i][22] == DBNull.Value) ? string.Empty : dataTable.Rows[i][22].ToString();
                model.IdTourDiemDen = (dataTable.Rows[i][23] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][23]);

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Remove panorama lon
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int RemovePanorama(ModelVYC01 model)
        {
            var query = "UPDATE [VYC-01] SET [HienThiPanoramaLon] = @hienthipanoramalon WHERE Id like @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = model.Id;
            sqlCommand.Parameters.Add("@hienthipanoramalon", SqlDbType.Bit).Value = model.HienThiPanoramaLon;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
        }

        public List<ModelVYC01> GetListPanoramaLon(int hienthi, int trangthai)
        {
            var list = new List<ModelVYC01>();
            try
            {
                var query = "SELECT * FROM [VYC-01] WHERE HienThiPanoramaLon = @hienthipanoramalon and TrangThai = @trangthai Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@hienthipanoramalon", SqlDbType.Bit).Value = hienthi;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = hienthi;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NgayDi = (DateTime) reader["NgayDi"];
                    // model.NoiDung = reader["NoiDung"].ToString();
                    model.Gia = Convert.ToInt32(reader["Gia"]);
                    // model.TacGia = reader["TacGia"].ToString();
                    // model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        public List<ModelVYC01> GetListTourDocDao()
        {
            var list = new List<ModelVYC01>();
            try
            {
                var query = "SELECT TOP 10 * FROM [VYC-01] WHERE [IdTourDocDao] Not Like '0' and TrangThai = 1 Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                // sqlCommand.Parameters.Add("@hienthipanoramalon", SqlDbType.Bit).Value = hienthi;
                // sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = hienthi;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NgayDi = (DateTime)reader["NgayDi"];
                    model.NgayDen = (DateTime)reader["NgayDen"];
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.Gia = Convert.ToInt32(reader["Gia"]);                    
                    // model.TacGia = reader["TacGia"].ToString();
                    // model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        public List<ModelVYC01> GetByUniqueId(string uniqueId, int trangthai)
        {
            var list = new List<ModelVYC01>();
            try
            {
                var query = "SELECT * FROM [VYC-01] WHERE UniqueId like @uniqueId and TrangThai = @trangthai Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@uniqueId", SqlDbType.VarChar).Value = uniqueId;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.NgayDi = (DateTime)reader["NgayDi"];
                    model.NgayDen = (DateTime)reader["NgayDen"];
                    model.IdNoiDi = Convert.ToInt32(reader["IdNoiDi"]);
                    model.IdNoiDen = Convert.ToInt32(reader["IdNoiDen"]);                    
                    model.Gia = Convert.ToInt32(reader["Gia"]);
                    model.Tag = reader["Tag"].ToString();
                    model.IdUser = reader["IdUser"].ToString();
                    model.IdTourDocDao = reader["IdTourDocDao"].ToString();
                    model.IdTourLeTet = reader["IdTourLeTet"].ToString();
                    model.IdTourDiemDen = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                    model.NgayCapNhat = (DateTime) reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// Tour co cung diem den
        /// </summary>
        /// <param name="diemden"></param>
        /// <param name="trangthai"></param>
        /// <returns></returns>
        public List<ModelVYC01> GetByDiemden(int diemden, int trangthai)
        {
            var list = new List<ModelVYC01>();
            try
            {
                var query = "SELECT * FROM [VYC-01] WHERE IdNoiDen = @idnoiden and TrangThai = @trangthai Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idnoiden", SqlDbType.VarChar).Value = diemden;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.NgayDi = (DateTime)reader["NgayDi"];
                    model.NgayDen = (DateTime)reader["NgayDen"];
                    model.IdNoiDi = Convert.ToInt32(reader["IdNoiDi"]);
                    model.IdNoiDen = Convert.ToInt32(reader["IdNoiDen"]);
                    model.Gia = Convert.ToInt32(reader["Gia"]);
                    model.Tag = reader["Tag"].ToString();
                    model.IdUser = reader["IdUser"].ToString();
                    model.IdTourDocDao = reader["IdTourDocDao"].ToString();
                    model.IdTourLeTet = reader["IdTourLeTet"].ToString();
                    model.IdTourDiemDen = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// Tour co cung muc gia
        /// </summary>
        /// <param name="gia"></param>
        /// <param name="trangthai"></param>
        /// <returns></returns>
        public List<ModelVYC01> GetByGia(decimal gia, int trangthai)
        {
            var list = new List<ModelVYC01>();
            try
            {
                var query = "SELECT * FROM [VYC-01] WHERE Gia <= @gia and TrangThai = @trangthai Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@gia", SqlDbType.Decimal).Value = gia;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.NgayDi = (DateTime)reader["NgayDi"];
                    model.NgayDen = (DateTime)reader["NgayDen"];
                    model.IdNoiDi = Convert.ToInt32(reader["IdNoiDi"]);
                    model.IdNoiDen = Convert.ToInt32(reader["IdNoiDen"]);
                    model.Gia = Convert.ToInt32(reader["Gia"]);
                    model.Tag = reader["Tag"].ToString();
                    model.IdUser = reader["IdUser"].ToString();
                    model.IdTourDocDao = reader["IdTourDocDao"].ToString();
                    model.IdTourLeTet = reader["IdTourLeTet"].ToString();
                    model.IdTourDiemDen = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// Get by ngay di
        /// </summary>
        /// <param name="ngaydi"></param>
        /// <param name="trangthai"></param>
        /// <returns></returns>
        public List<ModelVYC01> GetByNgayDi(DateTime ngaydi, int trangthai)
        {
            var list = new List<ModelVYC01>();
            try
            {
                var query = "SELECT * FROM [VYC-01] WHERE NgayDi like @ngaydi and TrangThai = @trangthai Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@ngaydi", SqlDbType.DateTime).Value = ngaydi;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.NgayDi = (DateTime)reader["NgayDi"];
                    model.NgayDen = (DateTime)reader["NgayDen"];
                    model.IdNoiDi = Convert.ToInt32(reader["IdNoiDi"]);
                    model.IdNoiDen = Convert.ToInt32(reader["IdNoiDen"]);
                    model.Gia = Convert.ToInt32(reader["Gia"]);
                    model.Tag = reader["Tag"].ToString();
                    model.IdUser = reader["IdUser"].ToString();
                    model.IdTourDocDao = reader["IdTourDocDao"].ToString();
                    model.IdTourLeTet = reader["IdTourLeTet"].ToString();
                    model.IdTourDiemDen = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// Get theo IDLoaiTour
        /// </summary>
        /// <param name="idlt"></param>
        /// <param name="trangthai"></param>
        /// <returns></returns>
        public List<ModelVYC01> GetTheoLoaiTour(int idlt, int trangthai)
        {
            var list = new List<ModelVYC01>();
            try
            {
                const string query = "SELECT TOP 10 * FROM [VYC-01] WHERE IdLoaiTour = @idloaitour and TrangThai = @trangthai Order By HienThiPanoramaNho DESC, NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idloaitour", SqlDbType.Int).Value = idlt;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.NgayDi = (DateTime)reader["NgayDi"];
                    model.NgayDen = (DateTime)reader["NgayDen"];
                    model.IdNoiDi = Convert.ToInt32(reader["IdNoiDi"]);
                    model.IdNoiDen = Convert.ToInt32(reader["IdNoiDen"]);                    
                    model.Gia = Convert.ToInt32(reader["Gia"]);
                    model.Tag = reader["Tag"].ToString();
                    model.IdUser = reader["IdUser"].ToString();                    
                    model.HienThiPanoramaLon = Convert.ToInt32(reader["HienThiPanoramaLon"]);
                    model.HienThiPanoramaNho = Convert.ToInt32(reader["HienThiPanoramaNho"]);
                    model.IdTourDocDao = reader["IdTourDocDao"].ToString();
                    model.IdTourLeTet = reader["IdTourLeTet"].ToString();
                    model.IdTourDiemDen = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        public List<ModelVYC01> GetTheoSubLoaitour(int idsublt, int trangthai)
        {
            var list = new List<ModelVYC01>();
            try
            {
                const string query = "SELECT * FROM [VYC-01] WHERE IdSubLoaiTour = @idsubloaitour and TrangThai = @trangthai Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idsubloaitour", SqlDbType.Int).Value = idsublt;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.NgayDi = (DateTime)reader["NgayDi"];
                    model.NgayDen = (DateTime)reader["NgayDen"];
                    model.IdNoiDi = Convert.ToInt32(reader["IdNoiDi"]);
                    model.IdNoiDen = Convert.ToInt32(reader["IdNoiDen"]);
                    model.Gia = Convert.ToInt32(reader["Gia"]);
                    model.Tag = reader["Tag"].ToString();
                    model.IdUser = reader["IdUser"].ToString();
                    model.IdTourDocDao = reader["IdTourDocDao"].ToString();
                    model.IdTourLeTet = reader["IdTourLeTet"].ToString();
                    model.IdTourDiemDen = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        public List<ModelVYC01> GetTheoLoaiTourvaSubloaitour(int idlt, int idsublt, int trangthai)
        {
            var list = new List<ModelVYC01>();
            try
            {
                const string query = "SELECT * FROM [VYC-01] WHERE IdLoaiTour = @idloaitour and IdSubLoaiTour = @idsubloaitour and TrangThai = @trangthai Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idloaitour", SqlDbType.Int).Value = idlt;
                sqlCommand.Parameters.Add("@idsubloaitour", SqlDbType.Int).Value = idsublt;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.NgayDi = (DateTime)reader["NgayDi"];
                    model.NgayDen = (DateTime)reader["NgayDen"];
                    model.IdNoiDi = Convert.ToInt32(reader["IdNoiDi"]);
                    model.IdNoiDen = Convert.ToInt32(reader["IdNoiDen"]);
                    model.Gia = Convert.ToInt32(reader["Gia"]);
                    model.Tag = reader["Tag"].ToString();
                    model.IdUser = reader["IdUser"].ToString();
                    model.IdTourDocDao = reader["IdTourDocDao"].ToString();
                    model.IdTourLeTet = reader["IdTourLeTet"].ToString();
                    model.IdTourDiemDen = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        public List<ModelVYC01> GetTourDocDao(int trangthai)
        {
            var list = new List<ModelVYC01>();
            try
            {
                const string query = "SELECT * FROM [VYC-01] WHERE IdTourDocDao != 0 and TrangThai = @trangthai Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);                              
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.NgayDi = (DateTime)reader["NgayDi"];
                    model.NgayDen = (DateTime)reader["NgayDen"];
                    model.IdNoiDi = Convert.ToInt32(reader["IdNoiDi"]);
                    model.IdNoiDen = Convert.ToInt32(reader["IdNoiDen"]);
                    model.Gia = Convert.ToInt32(reader["Gia"]);
                    model.Tag = reader["Tag"].ToString();
                    model.IdUser = reader["IdUser"].ToString();
                    model.IdTourDocDao = reader["IdTourDocDao"].ToString();
                    model.IdTourLeTet = reader["IdTourLeTet"].ToString();
                    model.IdTourDiemDen = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        public List<ModelVYC01> GetSubTourDocDao(int idsubtourdd, int trangthai)
        {
            var list = new List<ModelVYC01>();
            try
            {
                const string query = "SELECT * FROM [VYC-01] WHERE IdTourDocDao = @idloaitour and TrangThai = @trangthai Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idloaitour", SqlDbType.Int).Value = idsubtourdd;                
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.NgayDi = (DateTime)reader["NgayDi"];
                    model.NgayDen = (DateTime)reader["NgayDen"];
                    model.IdNoiDi = Convert.ToInt32(reader["IdNoiDi"]);
                    model.IdNoiDen = Convert.ToInt32(reader["IdNoiDen"]);
                    model.Gia = Convert.ToInt32(reader["Gia"]);
                    model.Tag = reader["Tag"].ToString();
                    model.IdUser = reader["IdUser"].ToString();
                    model.IdTourDocDao = reader["IdTourDocDao"].ToString();
                    model.IdTourLeTet = reader["IdTourLeTet"].ToString();
                    model.IdTourDiemDen = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        public List<ModelVYC01> GetByTag(string key, int trangthai)
        {
            var list = new List<ModelVYC01>();
            try
            {
                var query = "SELECT * FROM [VYC-01] WHERE Tag like @key and TrangThai = @trangthai Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@key", SqlDbType.NVarChar).Value = "%" + key + "%";
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC01();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.AnhPanorama = reader["AnhPanorama"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.NgayDi = (DateTime)reader["NgayDi"];
                    model.NgayDen = (DateTime)reader["NgayDen"];
                    model.IdNoiDi = Convert.ToInt32(reader["IdNoiDi"]);
                    model.IdNoiDen = Convert.ToInt32(reader["IdNoiDen"]);
                    model.Gia = Convert.ToInt32(reader["Gia"]);
                    model.Tag = reader["Tag"].ToString();
                    model.IdUser = reader["IdUser"].ToString();
                    model.IdTourDocDao = reader["IdTourDocDao"].ToString();
                    model.IdTourLeTet = reader["IdTourLeTet"].ToString();
                    model.IdTourDiemDen = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }
    }
}